package com.idefav.tosql;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.Console;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.io.RandomAccessFile;
import java.nio.channels.NonWritableChannelException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class exceltosql {

	public static void main(String[] args) throws Exception {

		if (args != null && args.length < 1) {
			System.out.println("参数错误");
		}

		// String inputfile=args[0];

		// String filename =
		// exceltosql.class.getResource("/DataBaseDesign.xlsx").getPath();
		// String template_path =
		// exceltosql.class.getResource("/sqltemplate.txt").getPath();
		// String outputpath="E:/sql/";
		String sheetindex=args[1];

		String filename = args[0];
		String template_path = "sqltemplate.txt";
		// String template = txt2String(new File(template_path));
		String template = readFile(template_path);
		InputStream inputStream = new FileInputStream(new File(filename));
		XSSFWorkbook workbook = new XSSFWorkbook(new File(filename));
		XSSFSheet sheet = workbook.getSheetAt(Integer.parseInt(sheetindex));

		String tablename = "";
		String tabledesc = "";
		String col_template = "[{name}] {type} {not} NULL,";
		String col_str = "";
		String col_desc_str = "";
		String col_descTemplate = "\r\nEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{desc}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'{table}', @level2type=N'COLUMN',@level2name=N'{name}'\r\nGO";
		Iterator<Row> iterator = sheet.rowIterator();
		boolean start = true;

		// 如果sql文件夹不存在创建
		File filesql = new File("sql");
		if (!filesql.exists()) {
			filesql.mkdirs();
		}

		while (iterator.hasNext()) {

			try {
				Row row = iterator.next();
				Iterator<Cell> cells = row.cellIterator();
				Cell cell1 = row.getCell(0);
				Cell cell2 = row.getCell(1);
				Cell cell3 = row.getCell(5);
				Cell cell4=row.getCell(3);
				Cell cell_notnull=row.getCell(4);
				Cell celltable = row.getCell(2);

				if (cell1 == null || cell2 == null || cell3 == null) {
					continue;
				}
				
				// 去除手动外键
				if(cell4!=null && cell4.getStringCellValue().toUpperCase().equals("FK")){
					continue;
				}
				String cellstr = cell1.getStringCellValue();
				if (cellstr != null && cellstr.equals("表名")) {
					if (!start) {
						// 保存文件
						String content = template.replace("{tablecol}", col_str).replace("{coldesc}", col_desc_str)
								.replace("{table}", tablename).replace("{tabledesc}", tabledesc);
						SimpleDateFormat simpleDateFormat=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
						content= content.replace("{date}", simpleDateFormat.format(new Date()));
						col_str = "";
						col_desc_str = "";
						// boolean writeTxtFile = writeTxtFile(content, new
						// File("sql/" + tablename + ".sql"));
						writeFile("sql/" + tablename + ".sql", content);
					}

					tablename = cell2.getStringCellValue();
					tabledesc = celltable.getStringCellValue();
					System.out.println("正在处理:"+tablename+"["+tabledesc+"]");
					start = true;
					continue;
				}
				if (cell1.getStringCellValue().equals("列名") || cell1 == null && cell2 == null || cell3 == null
						|| cell1.getStringCellValue().isEmpty()) {
					continue;
				}

				String colName = cell1.getStringCellValue();
				String colType = cell2.getStringCellValue();
				String colDesc = cell3.getStringCellValue();

				if (colName.equals("ID") || colName.equals("DataCreateTime") || colName.equals("DataChangeTime")
						|| colName.isEmpty()) {
					continue;
				}

				String tmp_col = col_template.replace("{name}", colName).replace("{type}", colType);
				if(cell_notnull!=null&& cell_notnull.getStringCellValue().equals("否")){
					tmp_col=tmp_col.replace("{not}", "NOT");
							
				}
				else{
					tmp_col=tmp_col.replace("{not}", "");
				}
				String tmp_desc_str = col_descTemplate.replace("{table}", tablename).replace("{name}", colName)
						.replace("{desc}", colDesc);

				col_str += "\t"+tmp_col+"\r\n";
				col_desc_str += tmp_desc_str;
				start = false;
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	public static boolean writeTxtFile(String content, File fileName) throws Exception {
		RandomAccessFile mm = null;
		boolean flag = false;
		FileOutputStream o = null;
		try {
			o = new FileOutputStream(fileName);
			o.write(content.getBytes("utf-8"));
			o.close();
			// mm=new RandomAccessFile(fileName,"rw");
			// mm.writeBytes(content);
			flag = true;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			if (mm != null) {
				mm.close();
			}
		}
		return flag;
	}

	/**
	 * 读取txt文件的内容
	 * 
	 * @param file
	 *            想要读取的文件对象
	 * @return 返回文件内容
	 */
	public static String txt2String(File file) {
		StringBuilder result = new StringBuilder();
		try {

			BufferedReader br = new BufferedReader(new FileReader(file));// 构造一个BufferedReader类来读取文件
			String s = null;
			while ((s = br.readLine()) != null) {// 使用readLine方法，一次读一行
				result.append(System.lineSeparator() + s);
			}
			br.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result.toString();
	}

	public static void writeFile(String fileName, String fileContent) {
		try {
			File f = new File(fileName);
			if (!f.exists()) {
				f.createNewFile();
			}
			OutputStreamWriter write = new OutputStreamWriter(new FileOutputStream(f), "utf-8");
			BufferedWriter writer = new BufferedWriter(write);
			writer.write(fileContent);
			writer.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static String readFile(String fileName) {
		String fileContent = "";
		try {
			File f = new File(fileName);
			if (f.isFile() && f.exists()) {
				InputStreamReader read = new InputStreamReader(new FileInputStream(f), "utf-8");
				BufferedReader reader = new BufferedReader(read);
				String line;
				while ((line = reader.readLine()) != null) {
					fileContent += line + "\r\n";
				}
				read.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return fileContent;
	}
}
